<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Gnc_db_mysql extends Model {
	protected $data_source;	// nama tabel pada database
	
	protected $global_hidden_columns = NULL;
	protected $local_hidden_columns = NULL;
	protected $hidden_columns = NULL;
	
	protected $global_hidden_columns_type = NULL;
	protected $local_hidden_columns_type = NULL;
	protected $hidden_columns_type = NULL;
	
	protected $global_auto_columns = NULL;
	protected $local_auto_columns = NULL;
	protected $auto_columns = NULL;
	
	protected $global_auto_columns_type = NULL;
	protected $local_auto_columns_type = NULL;
	protected $auto_columns_type = NULL;

	function __construct()
	{
		parent::Model();
		
		// ambil global hidden columns dari config
		$this->global_hidden_columns = $this->config->item('hidden_columns');
		$this->global_hidden_columns_type = $this->config->item('hidden_columns_type');

		// isi hidden columns (merge dari global dan lokal)				
		if (is_null_or_false($this->hidden_columns)) {			
			$this->populate_hidden_columns();
		}
		
		if (is_null_or_false($this->hidden_columns_type)) {			
			$this->populate_hidden_columns_type();
		}
		
		// ambil global auto-value columns dari config
		$this->global_auto_columns = $this->config->item('auto_columns');
		$this->global_auto_columns_type = $this->config->item('auto_columns_type');

		// isi auto-value columns (merge dari global dan lokal)		
		if (is_null_or_false($this->auto_columns)) {			
			$this->populate_auto_columns();		
		}
		
		if (is_null_or_false($this->auto_columns_type)) {			
			$this->populate_auto_columns_type();
		}		
	}

	public function populate_hidden_columns() {
		if (is_null_or_false($this->global_hidden_columns)) {
			$this->global_hidden_columns = array();
		}
		
		if (is_null_or_false($this->local_hidden_columns)) {
			$this->local_hidden_columns = array();
		}
		
		$this->hidden_columns = array_merge($this->global_hidden_columns, $this->local_hidden_columns);
	}
	
	public function populate_hidden_columns_type() {
		if (is_null_or_false($this->global_hidden_columns_type)) {
			$this->global_hidden_columns_type = array();
		}
		
		if (is_null_or_false($this->local_hidden_columns_type)) {
			$this->local_hidden_columns_type = array();
		}
		
		$this->hidden_columns_type = array_merge($this->global_hidden_columns_type, $this->local_hidden_columns_type);		
	}
	
	public function populate_auto_columns() {
		if (is_null_or_false($this->global_auto_columns)) {
			$this->global_auto_columns = array();
		}
		
		if (is_null_or_false($this->local_auto_columns)) {
			$this->local_auto_columns = array();
		}
		
		$this->auto_columns = array_merge($this->global_auto_columns, $this->local_auto_columns);		
	}
	
	public function populate_auto_columns_type() {
		if (is_null_or_false($this->global_auto_columns_type)) {
			$this->global_auto_columns_type = array();
		}
		
		if (is_null_or_false($this->local_auto_columns_type)) {
			$this->local_auto_columns_type = array();
		}
		
		$this->auto_columns_type = array_merge($this->global_auto_columns_type, $this->local_auto_columns_type);		
	}
	
	public function set_local_hidden_columns($columns, $repopulate = true, $merge = false) {
		if ($merge) {
			if (is_null_or_false($this->local_hidden_columns)) {
				$this->local_hidden_columns = array();
			}
			$this->local_hidden_columns = array_merge($this->local_hidden_columns, $columns);
		} else {
			$this->local_hidden_columns = $columns;
		}	
	
		if ($repopulate) {
			$this->populate_hidden_columns();
		}
	}
	
	public function set_local_hidden_columns_type($columns, $repopulate = true, $merge = false) {
		if ($merge) {
			if (is_null_or_false($this->local_hidden_columns_type)) {
				$this->local_hidden_columns_type = array();
			}
			$this->local_hidden_columns_type = array_merge($this->local_hidden_columns_type, $columns);
		} else {
			$this->local_hidden_columns_type = $columns;
		}	
	
		if ($repopulate) {
			$this->populate_hidden_columns_type();
		}
	}
	
	public function set_local_auto_columns($columns, $repopulate = true, $merge = false) {
		if ($merge) {
			if (is_null_or_false($this->local_auto_columns)) {
				$this->local_auto_columns = array();
			}
			$this->local_auto_columns = array_merge($this->local_auto_columns, $columns);
		} else {
			$this->local_auto_columns = $columns;
		}	
	
		if ($repopulate) {
			$this->populate_auto_columns();
		}
	}
	
	public function set_local_auto_columns_type($columns, $repopulate = true, $merge = false) {
		if ($merge) {
			if (is_null_or_false($this->local_auto_columns_type)) {
				$this->local_auto_columns_type = array();
			}
			$this->local_auto_columns_type = array_merge($this->local_auto_columns_type, $columns);
		} else {
			$this->local_auto_columns_type = $columns;
		}	
	
		if ($repopulate) {
			$this->populate_auto_columns_type();
		}
	}
	
	/**
	 * Mengeset variabel $this->$name dengan $value
	 *
	 * @param string $name  nama variabel yang akan di-assign
	 * @param mixed  $value nilai yang akan di-assign ke variabel
	 *
	 * @return void
	 * @access public
	 */
	public function __set($name, $value) {
		$this->$name = $value;
	}
	
	/**
	 * Mengambil nilai dari variabel $this->$name
	 *
	 * @param string $name nama variabel yang akan diambil nilainya
	 *
	 * @return mixed nilai dari $this->$name
	 * @access public
	 */
	public function __get($name) {
		$result = parent::__get($name);
		if (is_null($result)) {
			$result = $this->$name;
		}
		return $result;
	}
	
	/**
	 * Mengembalikan banyaknya field pada hasil query
	 *
	 * @param object $q CI_DB_mysql_result hasil query
	 *
	 * @return int sebuah integer yang merepresentasikan banyaknya field
	 * @access protected
	 */
	protected function get_n_fields($q) {
		return $q->num_fields();
	}
	
	/**
	 * Mengkonversi list of values pada enum dari format
	 * (a, b, c, d)
	 * menjadi
	 * array(a, b, c, d);
	 *
	 * @param 
	 */
	protected function get_enum_values($db_type, $comment = NULL) {	
		// ambil dari comment field
		if (!is_null($comment)) {
			$h = strrpos($comment, "(");
			if ($h!==false) {
				$db_type = substr($comment, $h+1);
			}
		}
	
		$po = strpos($db_type, '(');
		if ($po!==false) {
			$v = substr($db_type, $po);
			$v = substr($v, 1, -1);
			$v = explode(",", $v);
		} else {
			$v = array();
		}
		
		foreach ($v as $_i => $_v) {
			if ((strcmp(substr($_v, 0, 1), "'")==0) && (strcmp(substr($_v, -1), "'")==0)) {
				$v[$_i] = substr($_v, 1, -1);
			}
		}
		
		$result = array();
		
		foreach ($v as $_v) {
			$result[$_v] = $_v;
		}
		
		return $result;
	}
	
	protected function get_file_properties($comment) {
		//echo "sdisids $comment";
		if (!is_null($comment)) {
			$h = strrpos($comment, "/");
			if ($h!==false) {
				$properties = substr($comment, $h+1);
			}
		}
		
		$properties = explode(";", $properties);
		
		if (isset($properties[0])) {		
			$filename = $properties[0];
		} else {
			$filename = NULL;
		}
		if (isset($properties[1])) {
			$original_filename = $properties[1];
		} else {
			$original_filename = NULL;
		}
		
		$file_properties = compact('filename', 'original_filename');
		
		return $file_properties;
	}
	
	protected function get_image_properties($comment) {
		
		if (!is_null($comment)) {
			$h = strrpos($comment, "/");
			if ($h!==false) {
				$properties = substr($comment, $h+1);
			}
		}
		
		$properties = explode(";", $properties);
		
		$filename 			= $properties[0];
		$original_filename 	= $properties[1];
		
		$file_properties = compact('filename', 'original_filename');
		
		return $file_properties;
	}
	
	/**
	 * Mengembalikan hasil konversi tipe internal db ke tipe standar SI:
	 * DATE, DATETIME, TIME, STRING, PASSWORD, TEXT, INTEGER, REAL,
	 * NUMERIC, CURRENCY, BOOLEAN, ENUM, FILE, IMAGE
	 * atau UNKNOWN jika tidak ditemukan tipe yang sesuai
	 *
	 * comment: alias#data_type(length)/data_properties
	 *
	 * @param string $db_type tipe field dari database/hasil mysql_fetch_field
	 * @param string $comment komentar field pada database
	 *
	 * @return string sebuah string yang merupakan hasil konversi tipe
	 * @access protected
	 */
	protected function get_field_type($db_type, $comment = NULL) {
		$type = NULL;
		
		// ambil dari comment field
		$comment = strtoupper($comment);
		if (!is_null($comment)) {
			$h = strrpos($comment, "#");
			if ($h!==false) {
				$db_type = substr($comment, $h+1);
			}
		}
		
		$t = $db_type;
		
		$po = strpos($db_type, '(');
		if ($po!==false) {
			$t = substr($db_type, 0, $po);
		}	
		
		$po = strrpos($db_type, '/');
		if ($po!==false) {
			$t = substr($db_type, 0, $po);
		}
		
		
		// jika tidak ada, derive dari tipe data pada db		
		switch ($t) {
			case "date":
				$type = "DATE"; break;
			case "datetime":
				$type = "DATETIME"; break;
			case "time":
				$type = "TIME"; break;	
			case "char":
				$type = "STRING"; break;
			case "varchar":
				$type = "STRING"; break;
			case "string":
				$type = "STRING"; break;
			case "text":
				$type = "TEXT"; break;
			case "int":
				$type = "INTEGER"; break;
			case "float":
				$type = "REAL"; break;
			case "double":
				$type = "REAL"; break;
			case "decimal":
				$type = "NUMERIC"; break;
			case "numeric":
				$type = "NUMERIC"; break;
			case "tinyint":
				if ($this->get_field_size($db_type)==1) {
					$type = "BOOLEAN";
				} else {
					$type = "INTEGER";
				}
				break;
			case "enum":
				$type = "ENUM"; break;
			case "blob":
				$type = "FILE"; break;
			case "longblob":
				$type = "FILE"; break;
			default:
				if (strcmp($t, strtoupper($t))==0) { // all caps
					$type = $t; // tipe dari komentar
				} else {
					$type = "UNKNOWN";
				}
		}
		
		return $type;
	}
	
	/**
	 * Mengembalikan ukuran field berdasarkan tipe standar SI yang dicantumkan
	 * pada bagian comment, atau dari internal db jika tipe tidak didefinisikan
	 * pada comment
	 *
	 * comment: alias#data_type(length)/data_properties
	 *
	 * @param string $db_type tipe field dari database/hasil mysql_fetch_field
	 * @param string $comment komentar field pada database
	 *
	 * @return int sebuah integer yang merepresentasikan ukuran field, atau
	 *             NULL jika ukuran tidak terdefinisi
	 * @access protected
	 */
	protected function get_field_size($db_type, $comment = NULL) {
		// ambil dari comment field
		if (!is_null($comment)) {
			$h = strrpos($comment, "(");
			if ($h!==false) {
				$db_type = substr($comment, $h+1);
			}
		}
		
		$po = strpos($db_type, '(');
		$pc = strpos($db_type, ')');
		if (($po!==false) && ($pc!==false)) {
			$size = substr($db_type, $po+1, $pc-$po-1);
		} else {
			$size = NULL;
		}
		return $size;
	}
	
	/**
	 * Mengembalikan alias suatu field yang diperoleh dari kolom comment
	 * pada field tabel
	 *
	 * comment: alias#data_type(length)/data_properties
	 * 
	 * @param string $comment komentar field pada database
	 *
	 * @return string string yang merupakan alias dari nama field
	 * @access protected
	 */
	protected function get_field_alias($comment) {
		$h = strrpos($comment, "#");
		if ($h!==false) {
			$alias = substr($comment, 0, $h);
		} else {
			$s = strrpos($comment, "/");
			if ($s!==false) {
				$alias = substr($comment, 0, $s);
			} else {
				$alias = $comment;
			}
		}
		return $alias;
	}
	
	/**
	 * Mengembalikan properti masing-masing field
	 *
	 * Tiap field terdiri atas:
	 *		- ['name']		= nama field pada tabel
	 *		- ['alias']		= displayed name dari field
	 *		- ['db_type']	= tipe field tabel pada database	 
	 *		- ['type']		= tipe field tabel (converted)
	 *		- ['size']		= size field tabel
	 *		- ['values']	= list of possible values, only for ENUM
	 *		- ['nullable']	= if true: cannot enter null as value
	 *		- ['editable']	= if false: read only
	 *		- ['viewable']	= if false: hidden
	 *		- ['autovalue'] = if false: hidden from add/update form
	 *
	 * @param object $q CI_DB_mysql_result hasil query
	 *
	 * @return array array yang berisi properti seluruh field tabel
	 * @access protected
	 */
	protected function get_fields($q) {
		$fields = array();

		mysql_field_seek($q->result_id, 0);		
		while ($field = mysql_fetch_field($q->result_id)) {
			
			$t  = $field->table;
			$fn = $field->name;
										
			if ((is_string($t)) && (strlen($t)>0) && ($this->db->field_exists($fn, $t))) {
				$f = $this->db->query('SHOW FULL COLUMNS FROM '.$t.' WHERE Field = '.$this->db->escape($fn));
				$cf = current($f->result_array());				
				
				//print_r_pre($cf);
				
				$name		= $cf['Field'];
				$table		= $t;
				$comment	= $cf['Comment'];
				$alias		= $this->get_field_alias($comment);
				$db_type	= $cf['Type'];
				$type		= $this->get_field_type($db_type, $comment);
				$size		= $this->get_field_size($db_type, $comment);
				$nullable	= (strcasecmp($cf['Null'], 'YES')==0);
				$editable	= !(strcasecmp($cf['Key'], 'PRI')==0);
				$viewable	= !in_arrayi($name, $this->hidden_columns) && !in_arrayi($type, $this->hidden_columns_type);
				$autovalue	= in_arrayi($name, $this->auto_columns) || in_arrayi($type, $this->auto_columns_type);
				//echo "aaaaaaaaaaaaaaaa $name $type @".$comment."<br>";
				$f->free_result();
			} else {	// field tidak ada di schema, biasanya hasil kalkulasi/rename
				$f = $field;
				$name		= $f->name;
				$table		= $t;
				$alias		= '';
				$db_type	= $f->type;
				$type		= $this->get_field_type($db_type);
				$size		= $this->get_field_size($db_type);
				$nullable	= !$f->not_null;
				$editable	= false;
				$viewable	= !in_arrayi($name, $this->hidden_columns) && !in_arrayi($type, $this->hidden_columns_type);
				$autovalue	= in_arrayi($name, $this->auto_columns) || in_arrayi($type, $this->auto_columns_type);
			}

			if (isset($values)) {
				unset($values);
			}
			
			if (strcasecmp($type, 'ENUM')==0) {
				$values	= $this->get_enum_values($db_type, $comment);
			}
			
			if (isset($properties)) {
				unset($properties);
			}
			
			if (strcasecmp($type, 'FILE')==0) {
				//echo "masuk sini";
				$properties	= $this->get_file_properties($comment);
				//print_r($properties);
			}
			
			if (strcasecmp($type, 'IMAGE')==0) {
				$properties	= $this->get_image_properties($comment);
			}
			
			$field    = compact('name', 'table', 'alias', 'db_type', 'type', 'size', 'properties', 'values', 'nullable', 'editable', 'viewable', 'autovalue');
			$fields[] = $field;
		}
		
		return $fields;
	}

	protected function get_fields_aliases($fields) {
		$aliases = array();
		foreach ($fields as $fi => $fv) {
			if (is_empty($fv['alias'])) {
				$fv['alias'] = $fv['name'];
			}
			$aliases[$fv['name']] = $fv['alias'];
		}
		return $aliases;
	}

	protected function get_fields_reverse_aliases($fields) {
		$aliases = array();
		foreach ($fields as $fi => $fv) {
			if (is_empty($fv['alias'])) {
				$fv['alias'] = $fv['name'];
			}
			$aliases[$fv['alias']] = $fv['name'];
		}
		return $aliases;
	}	
	
	protected function get_fields_list($fields) {
		$fields_list = array();
		foreach ($fields as $fi => $fv) {
			$fields_list[] = $fv['name'];
		}
		return $fields_list;
	}
	
	protected function get_fields_numbers($fields) {
		$_fields_list = array();
		foreach ($fields as $fi => $fv) {
			$_fields_list[] = $fv['name'];
		}
		$fields_list = array();
		foreach ($_fields_list as $fi => $fv) {
			$fields_list[$fv] = $fi;
		}
		return $fields_list;
	}
	
	protected function get_keys_from_constraint_name($table_name, $constraint_name) {
		$kv = $this->db->query('SELECT DISTINCT `COLUMN_NAME` FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = '.$this->db->escape($table_name).' AND CONSTRAINT_NAME = '.$this->db->escape($constraint_name));
		$kvl = $kv->result_array();
		$k = array();
		foreach ($kvl as $kvli => $kvlv) {
			$k[] = $kvlv['COLUMN_NAME'];
		}
		if (count($k)==1) {
			reset($k);
			$k = current($k);
		}
		return $k;
	}
	
	protected function get_fk_table_from_constraint_name($table_name, $constraint_name) {
		$tv = $this->db->query('SELECT DISTINCT `REFERENCED_TABLE_NAME` FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = '.$this->db->escape($table_name).' AND CONSTRAINT_NAME = '.$this->db->escape($constraint_name));
		$tvl = $tv->result_array();
		$t = array();
		foreach ($tvl as $tvli => $tvlv) {
			$t[] = $tvlv['REFERENCED_TABLE_NAME'];
		}
		reset($t);
		$t = current($t);
		return $t;
	}
	
	protected function get_fk_value_from_constraint_name($table_name, $constraint_name) {
		$kv = $this->db->query('SELECT DISTINCT `COLUMN_NAME`, `REFERENCED_COLUMN_NAME` FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = '.$this->db->escape($table_name).' AND CONSTRAINT_NAME = '.$this->db->escape($constraint_name));
		$kvl = $kv->result_array();
		$k = array();
		foreach ($kvl as $kvli => $kvlv) {
			$k[$kvlv['COLUMN_NAME']] = $kvlv['REFERENCED_COLUMN_NAME'];
		}
		return $k;
	}
	
	/**
	 * Mengembalikan daftar key yang terdapat pada tabel
	 *
	 * Daftar key terdiri atas:
	 *		- ['primary']	=> Primary Keys
	 *			-- [table]					of table 'table'
	 *			--- ['name']  = key name, usually PRIMARY
	 *			--- ['value'] = single value or array if composite
	 *		- ['alt']		=> Alternative Keys
	 *			-- [table]					of table 'table'
	 *			--- ['name']  = key name, usually KEY
	 *			--- ['value'] = single value or array if composite
	 *		- ['foreign']	=> Foreign Keys, array of key and the referee
	 *			-- [table]					of table 'table'
	 *			--- ['name']  = key name, usually FK_...
	 *			--- ['value'] = single value or array if composite
	 *			--- ['fk_table'] = foreign table name
	 *			--- ['fk_value'] = associative array (field => foreign field)
	 *
	 * @param object $q CI_DB_mysql_result hasil query
	 *
	 * @return array array yang berisi properti seluruh field tabel
	 * @access protected
	 */
	protected function get_keys($q) {
		$keys = array();
	
		$pt = $this->get_participating_tables($q);
		$tl = $pt['tables_list'];
		$tfn = $pt['fields_list'];
	
		$cl = array();
		$primary = array();
		$alt = array();
		$foreign = array();
		
		foreach ($tl as $tli => $tlv) { // foreach table involved in query
			$c = $this->db->query('SELECT DISTINCT `CONSTRAINT_NAME`, `CONSTRAINT_TYPE` FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME = '.$this->db->escape($tlv));
			$cl[$tlv] = $c->result_array();
			
			$id_field_found = false;
			$key_field_found = false;
			
			$n_u  = 0;
			$n_fk = 0;
			
			foreach($cl[$tlv] as $cli => $clv) {
				// of table $tlv:
				// $clv['CONSTRAINT_TYPE']; 'PRIMARY KEY', 'UNIQUE', 'FOREIGN KEY'
				// $clv['CONSTRAINT_NAME'];
				switch ($clv['CONSTRAINT_TYPE']) {
					case 'PRIMARY KEY':
						$primary[$tlv]['name'] = $clv['CONSTRAINT_NAME'];
						$primary[$tlv]['value'] = $this->get_keys_from_constraint_name($tlv, $clv['CONSTRAINT_NAME']);
						$id_field_found = $id_field_found || (is_string($primary[$tlv]['value']) && (strcasecmp($primary[$tlv]['value'], 'id')==0));
						break;
					case 'UNIQUE':
						$alt[$tlv][$n_u]['name'] = $clv['CONSTRAINT_NAME'];
						$alt[$tlv][$n_u]['value'] = $this->get_keys_from_constraint_name($tlv, $clv['CONSTRAINT_NAME']);						
						$key_field_found = $key_field_found || (is_string($alt[$tlv]['value']) && (strcasecmp($alt[$tlv]['value'], 'key')==0));				
						$n_u++;
						break;
					case 'FOREIGN KEY':
						$foreign[$tlv][$n_fk]['name'] = $clv['CONSTRAINT_NAME'];
						$foreign[$tlv][$n_fk]['value'] = $this->get_keys_from_constraint_name($tlv, $clv['CONSTRAINT_NAME']);
						$foreign[$tlv][$n_fk]['fk_table'] = $this->get_fk_table_from_constraint_name($tlv, $clv['CONSTRAINT_NAME']);
						$foreign[$tlv][$n_fk]['fk_value'] = $this->get_fk_value_from_constraint_name($tlv, $clv['CONSTRAINT_NAME']);
						$n_fk++;
						break;
				}								
			}
			
			//var_dump($id_field_found);
			//var_dump($key_field_found);
					
			// by convention, fields ID and KEY are keys
			if (!$id_field_found) {
				if (in_arrayi('id', $tfn[$tlv])) {
					$primary[$tlv]['name'] = 'C_ID';
					$primary[$tlv]['value'] = case_insensitive_search('id', $tfn[$tlv]);
					//$id_field_found = true;
				}
			}
			
			if (!$key_field_found) {
				if (in_arrayi('key', $tfn[$tlv])) {
					$alt[$tlv][$n_u]['name'] = 'C_KEY';
					$alt[$tlv][$n_u]['value'] = case_insensitive_search('key', $tfn[$tlv]);
					//$key_field_found = true;
					$n_u++;
				}
			}
			
			// no keys found, create a key consisting of all fields
			if ((count($primary)==0) && (count($alt)==0)) {
				$alt[$tlv]['name'] = 'A_KEY';
				$alt[$tlv]['value'] = $tfn[$tlv];
			}
		}
				
		$keys = compact('primary', 'alt', 'foreign');

		return $keys;
	}
	
	protected function get_participating_tables($q) {
		// cari daftar tabel yang terkait ($tl) dan field2 terkait untuk masing-masing tabel ($tfn)
		mysql_field_seek($q->result_id, 0);
		$tfn = array();
		$tl = array();
		while ($field = mysql_fetch_field($q->result_id)) {
			$t  = $field->table;
			$fn = $field->name;

			$tl[]      = $t;
			$tfn[$t][] = $fn;
		}
		
		$tl = array_unique($tl); // $tl = list semua tabel yg terkait

		foreach ($tfn as $tfni => $tfnv) {
			$tfn[$tfni] = array_unique($tfnv); // $tfn[x] = list semua field terkait pada tabel x
		}		
		
		$tables_list = $tl;
		$fields_list = $tfn;
		
		$result = compact('tables_list', 'fields_list');
			
		return $result;
	}
	
	protected function get_n_tables($q) {
		$pt = $this->get_participating_tables($q);
		$tl = $pt['tables_list'];

		$n_tables = count($tl); // $n_tables = banyaknya tabel yang terkait
		
		return $n_tables;
	}

	protected function get_pri_table($q) {	
		$pt = $this->get_participating_tables($q);
		$tl = $pt['tables_list'];
		$tfn = $pt['fields_list'];
	
		// cari primary table dari query $q
		// heuristic:
		// primary table adalah table yg dispecify di $data_source
		//                 atau table yg punya lebih banyak field pada hasil
		
		if (in_arrayi($this->data_source, $tl)) {
			$pri_table = $this->data_source;
		} else {	
			reset($tfn);
			$pt  = key($tfn);
			$ptc = count(current($tfn));
			
			foreach ($tfn as $tfni => $tfnv) {
				if (count($tfnv)>$ptc) {
					$pt  = $tfni;
					$ptc = count($tfnv);
				}
			}
			$pri_table = $pt;
		}
		
		return $pri_table;
	}
	
	/**
	 * Mengembalikan informasi tentang field-field pada tabel di database
	 *
	 * Format:
	 *		['query']		= statement query yang dieksekusi
	 *		['n_tables']	= no of table involved
	 *		['pri_table']	= primary table (= $data_source or one with more fields if from query)	 
	 *		['n_fields']	= banyaknya field pada tabel
	 *		['fields']		= array berisi informasi field-field tabel
	 *		['fields_list']	= array berisi daftar field-field tabel	 
	 *		['fields_aliases']
	 *						= array berisi mapping alias field-field tabel
	 *		['keys']		= array berisi keys dari tabel
	 *
	 * @param string $query string yang berisi query SQL yang akan dieksekusi
	 *
	 * @return array array yang berisi metadata tabel pada database
	 * @access protected
	 * @see get_n_fields(), get_fields(), get_keys()
	 */
	protected function get_metadata($query = NULL) {
		// initializations
		if (is_null($query)) {
			$query = $this->build_select_query();
		}
		$n_fields = NULL; $fields = NULL; $keys = NULL;
		
		if (!is_null($query)) {
			$q		 	= $this->db->query($query);
			$n_tables	= $this->get_n_tables($q);
			$pri_table	= $this->get_pri_table($q);
			$n_fields	= $this->get_n_fields($q);
			$fields		= $this->get_fields($q);
			$fields_list			= $this->get_fields_list($fields);
			$fields_numbers			= $this->get_fields_numbers($fields);
			$fields_aliases			= $this->get_fields_aliases($fields);
			$fields_reverse_aliases	= $this->get_fields_reverse_aliases($fields);
			$keys		= $this->get_keys($q);
			$q->free_result();
		}
		return compact('query', 'n_tables', 'pri_table', 'n_fields', 'fields', 'fields_list', 'fields_numbers', 'fields_aliases', 'fields_reverse_aliases', 'keys');
	}
	
	/**
	 * Mengembalikan record-record pada tabel di database
	 *
	 * Format:
	 *		['n_records']		= banyaknya record hasil query
	 *		['n_all_records']	= banyaknya record pada table
	 *		['records']			= array berisi records hasil query
	 *
	 * @param string $query string yang berisi query SQL yang akan dieksekusi
	 *
	 * @return array array yang merepresentasikan isi dari record-
	 *               record tabel pada database
	 * @access protected
	 */
	protected function get_data($query = NULL) {
		if (is_null($query)) {
			$query = $this->build_select_query();
		}
		
		if (!is_null($query)) {
			$q = $this->db->query($query);
			$n_records 	= $q->num_rows();
			$records 	= $q->result_array();
			$q->free_result();
			
			$limit = strpos($query, ' LIMIT');
			
			if ($limit) {
				$query = substr($query, 0, $limit);
			}

			$q = $this->db->query($query);
			$n_all_records 	= $q->num_rows();
			$q->free_result();
		}
		return compact('n_records', 'n_all_records', 'records');
	}
	
	protected function build_select_query($select = NULL, $from = NULL, $where = NULL, $group_by = NULL, $having = NULL, $order_by = NULL, $limit = NULL) {
		$query = NULL;

		if (is_null($select)) {
			$select = '*';
		}
		
		if (is_null($from)) {
			$from = $this->data_source;
		}
		
		$query = 'SELECT '.$select;
		$query .= ' FROM '.$from;
		if (!is_null($where)) {
			$query .= ' WHERE '.$where;
		}
		if (!is_null($group_by)) {
			$query .= ' GROUP BY '.$group_by;
		}
		if (!is_null($having)) {
			$query .= ' HAVING '.$having;
		}
		if (!is_null($order_by)) {
			$query .= ' ORDER BY '.$order_by;
		}
		if (!is_null($limit)) {
			$query .= ' LIMIT '.$limit;
		}
			
		return $query;
	}
		
	protected function compile_select($select) {
		$ss = '';
		if (is_array($select)) {
			if (is_empty($select)) {
				$ss = NULL;
			} else {
				foreach($select as $si => $sv) {
					if (is_numeric($si)) {
						$ss .= ''.$sv.', ';
					} else {
						$ss .= ''.$si.' AS '.$sv.', ';
					}
				}
				if (strlen($ss)>0) {
					$ss = substr($ss, 0, -2);
				}
			}
		} else if (is_string($select)) {
			$ss = $select;
		} else if (is_null($select)) {
			$ss = NULL;
		}
		
		return $ss;
	}
	
	protected function compile_where($where) {
		$ws = '';
		if (is_array($where)) {
			if (is_empty($where)) {
				$ws = NULL;
			} else {
				foreach($where as $wi => $wv) {
					$wip = explode(" ", $wi);									
					if (!is_null($wv)) {
						if (count($wip>0) && (in_arrayi($wip[count($wip)-1], array('=', '>', '>=', '<', '<=', 'LIKE')))) {
							$operator = $wip[count($wip)-1];
							unset($wip[count($wip)-1]);
							$wii = implode(" ", $wip);
							$ws .= '`'.$wii.'` '.$operator.' '.$this->db->escape($wv).' AND ';
						} else {
							$ws .= '`'.$wi.'`='.$this->db->escape($wv).' AND ';
						}
					} else {
						$ws .= '`'.$wi.'` IS NULL AND ';
					}
				}
				if (strlen($ws)>0) {
					$ws = substr($ws, 0, -5);
				}
			}
		} else if (is_string($where)) {
			$ws = $where;
		} else if (is_null($where)) {
			$ws = NULL;
		} else if (is_bool($where)) {
			if ($where) {
				$ws = 'TRUE';
			} else {
				$ws = 'FALSE';
			}
		}
		return $ws;
	}

	protected function compile_limit($limit) {
		$ls = '';
		if (is_array($limit)) {
			if (is_empty($limit)) {
				$ls = NULL;
			} else {
				$ls = $limit['offset'].', '.$limit['limit'];		
			}
		} else if (is_string($limit)) {
			$ls = $limit;
		} else if (is_null($limit)) {
			$ls = NULL;
		}
		return $ls;
	}
	
	protected function compile_order_by($order_by) {
		$os = '';
		if (is_array($order_by)) {
			if (is_empty($order_by)) {
				$os = NULL;
			} else {
				foreach($order_by as $oi => $ov) {
					if (is_numeric($oi)) {
						$os .= '`'.$ov.'`, ';
					} else {
						$os .= '`'.$oi.'` '.strtoupper($ov).', ';
					}
				}
				if (strlen($os)>0) {
					$os = substr($os, 0, -2);
				}
			}
		} else if (is_string($order_by)) {
			$os = $order_by;
		} else if (is_null($order_by)) {
			$os = NULL;
		}
		return $os;
	}

	protected function compile_group_by($group_by) {
		$gs = '';
		if (is_array($group_by)) {
			if (is_empty($group_by)) {
				$gs = NULL;
			} else {
				foreach($group_by as $gi => $gv) {
					$gs .= '`'.$gv.'`, ';
				}
				if (strlen($gs)>0) {
					$gs = substr($gs, 0, -2);
				}
			}
		} else if (is_string($group_by)) {
			$gs = $group_by;
		} else if (is_null($group_by)) {
			$gs = NULL;
		}
		return $gs;
	}

	protected function compile_having($having) {
		$hs = '';
		if (is_array($having)) {
			if (is_empty($having)) {
				$hs = NULL;
			} else {
				foreach($having as $hi => $hv) {
					if (!is_null($hv)) {
						$hs .= '`'.$hi.'`='.$this->db->escape($hv).' AND ';
					} else {
						$hs .= '`'.$hi.'` IS NULL AND ';
					}
				}
				if (strlen($hs)>0) {
					$hs = substr($hs, 0, -5);
				}
			}
		} else if (is_string($having)) {
			$hs = $having;
		} else if (is_null($having)) {
			$hs = NULL;
		} else if (is_bool($having)) {
			if ($having) {
				$hs = 'TRUE';
			} else {
				$hs = 'FALSE';
			}
		}
		return $hs;
	}
	
	protected function compile_from($from) {
		$fs = '';
		if (is_array($from)) {
			if (is_empty($from)) {
				$fs = NULL;
			} else {
				foreach($from as $fi => $fv) {
					$fs .= $fv.', ';
				}
				if (strlen($fs)>0) {
					$fs = substr($fs, 0, -2);
				}
			}
		} else if (is_string($from)) {
			$fs = $from;
		} else if (is_null($from)) {
			$fs = NULL;
		}
		return $fs;
	}

	public function get_from_sql($query = NULL, $include_meta = true, $include_data = true) {	
		if (is_null($query)) {
			$query = $this->build_select_query();
		}
		
		if (!is_null($query)) {
			if ($include_meta) {
				$metadata	= $this->get_metadata($query);
			}
			
			if ($include_data) {
				$data		= $this->get_data($query);
			}
			$result		= compact('metadata', 'data');
		} else {
			$result		= NULL;
		}
		
		return $result;
	}
	
	public function get_from_query($select = NULL, $where = NULL, $limit = NULL, $order_by = NULL, $group_by = NULL, $having = NULL, $from = NULL, $include_meta = true, $include_data = true) {	
	
		$select		= $this->compile_select($select);
		$where		= $this->compile_where($where);
		$limit		= $this->compile_limit($limit);		
		$order_by	= $this->compile_order_by($order_by);
		$group_by	= $this->compile_group_by($group_by);
		$having		= $this->compile_having($having);
		$from		= $this->compile_from($from);
	
		$query	= $this->build_select_query($select, $from, $where, $group_by, $having, $order_by, $limit);
		//echo $query;
		return $this->get_from_sql($query, $include_meta, $include_data);
	}
	
	public function get_data_from_query($select = NULL, $where = NULL, $limit = NULL, $order_by = NULL, $group_by = NULL, $having = NULL, $from = NULL) {
		$result = $this->get_from_query($select, $where, $limit, $order_by, $group_by, $having, $from, false, true);
		if (isset($result['data']['records'])) {
			$result = $result['data']['records'];
		} else {
			$result = false;
		}
		return $result;
	}
	
	public function get_data_from_sql($query = NULL) {	
		$result = $this->get_from_sql($query, false, true);
		if (isset($result['data']['records'])) {
			$result = $result['data']['records'];
		} else {
			$result = false;
		}
		return $result;
	}
	
	public function get_metadata_from_query($select = NULL, $where = NULL, $limit = NULL, $order_by = NULL, $group_by = NULL, $having = NULL, $from = NULL) {
		$result = $this->get_from_query($select, $where, $limit, $order_by, $group_by, $having, $from, true, false);
		if (isset($result['metadata'])) {
			$result = $result['metadata'];
		} else {
			$result = false;
		}
		return $result;
	}
	
	public function get_metadata_from_sql($query = NULL) {	
		$result = $this->get_from_sql($query, true, false);
		if (isset($result['metadata'])) {
			$result = $result['metadata'];
		} else {
			$result = false;
		}
		return $result;
	}
	
	public function get($query = NULL, $include_meta = true, $include_data = true) {
		return $this->get_from_sql($query, $include_meta, $include_data);
	}
	
	public function get_single($record) {
		if (!is_array($record)) {
			$record = array('id' => NULL);
		}
		//XXX: assuming table PK is conforming to our convention
		$metadata = $this->get_metadata($this->build_select_query());
		
		$data['n_all_records'] = $this->db->count_all($this->data_source);
		$data['first'] = current($this->db->query('SELECT * FROM '.$this->data_source.' ORDER BY `'.key($record).'` ASC LIMIT 1')->result_array());
		$data['previous'] = current($this->db->query('SELECT * FROM '.$this->data_source.' WHERE `'.key($record).'` < '.$this->db->escape(current($record)).' ORDER BY `'.key($record).'` DESC LIMIT 1')->result_array());
		$data['current'] = current($this->db->query('SELECT * FROM '.$this->data_source.' WHERE '.$this->compile_where($record))->result_array());
		$data['next'] = current($this->db->query('SELECT * FROM '.$this->data_source.' WHERE `'.key($record).'` > '.$this->db->escape(current($record)).' ORDER BY `'.key($record).'` ASC LIMIT 1')->result_array());
		$data['last'] = current($this->db->query('SELECT * FROM '.$this->data_source.' ORDER BY `'.key($record).'` DESC LIMIT 1')->result_array());
		$result		= compact('metadata', 'data');
		return $result;
	}	
	
	protected function build_delete_query($from = NULL, $where = NULL) {
		$query = NULL;
		
		if (is_null($from)) {
			$from = $this->data_source;
		}
		
		if (is_array($where)) {
			$where = $this->compile_where($where);
		}
		
		$query = 'DELETE FROM '.$from;
		if (!is_null($where)) {
			$query .= ' WHERE '.$where;
		}
		
		return $query;
	}
	
	public function delete($rID) {
		//TODO: insert to tabel history
		$query = $this->build_delete_query(NULL, $this->compile_where($rID));
		return $this->db->query($query);
	}

	protected function build_insert_query($into = NULL, $data = NULL) {
		$query = NULL;
		
		if (is_null($into)) {
			$into = $this->data_source;
		}
		
		if (!(is_null($data) || is_empty($data))) {
			$query = 'INSERT INTO '.$into.' (';
			foreach ($data as $idx => $val) {
				$query .= '`'.$idx.'`, ';
			}
			
			if (strcmp(substr($query, -2), ', ')==0) {
				$query = substr($query, 0, -2);
			}
			
			$query .= ') VALUES (';
			foreach ($data as $idx => $val) {
				$query .= $this->db->escape($val).', ';
			}
			
			if (strcmp(substr($query, -2), ', ')==0) {
				$query = substr($query, 0, -2);
			}
			
			$query .= ')';
		}
		
		return $query;
	}
	
	protected function build_update_query($table = NULL, $data = NULL, $where = NULL) {
		$query = NULL;
		
		if (is_null($table)) {
			$table = $this->data_source;
		}
		
		if (is_array($where)) {
			$where = $this->compile_where($where);
		}
		
		if ((!(is_null($data) || is_empty($data))) && (!(is_null($where) || is_empty($where)))) {
			$query = 'UPDATE '.$table.' SET ';
			foreach ($data as $idx => $val) {
				$query .= '`'.$idx.'`='.$this->db->escape($val).', ';
			}
			if (strcmp(substr($query, -2), ', ')==0) {
				$query = substr($query, 0, -2);
			}
			$query .= ' WHERE '.$where;
		}
		
		return $query;
	}
	
	public function add($data, $table = NULL) {
		$query = $this->build_insert_query($table, $data);
		//echo $query;
		return $this->db->query($query);
	}
	
	public function update($data, $record, $table = NULL) {
		$query = $this->build_update_query($table, $data, $record);
		//echo $query;
		return $this->db->query($query);
	}
}

/* EOF models/gnc_db_mysql.php */